﻿/*
    Copyright (c) 2012, Matías López
    All rights reserved.

    Redistribution and use in source and binary forms, with or without modification, are permitted 
    provided that the following conditions are met:
        *   Redistributions of source code must retain the above copyright notice, this list of 
            conditions and the following disclaimer.
        *   Redistributions in binary form must reproduce the above copyright notice, this list of 
            conditions and the following disclaimer in the documentation and/or other materials 
            provided with the distribution.
        *   Neither the name of Matías López nor the names of its contributors may be used to 
            endorse or promote products derived from this software without specific prior written 
            permission.
    
    THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR 
    IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY 
    AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR 
    CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR 
    CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR 
    SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY 
    THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR 
    OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE 
    POSSIBILITY OF SUCH DAMAGE.
 */

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace mana.Data.Sql.Schema
{
    public class SqlSchemaInspector
    {
        #region Attributes

        private SqlConnectionStringBuilder _connectionStringBuilder;
        private string _connectionString;

        #endregion Attributes

        #region Constructors

        public SqlSchemaInspector(string connectionString)
	    {
            if (string.IsNullOrWhiteSpace(connectionString))
                throw new ArgumentNullException("connectionString");

            _connectionString = connectionString;
            _connectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
	    }

        public string CurrentDatabase
        {
            get { return _connectionStringBuilder.InitialCatalog; }
        }

        #endregion Constructors

        public string ConnectionString
        {
            get { return _connectionString; }
        }

        #region Methods

        #region Public

        public SqlDatabaseSchema GetDatabase(string name)
        {
            return GetDatabasesSchema(name).SingleOrDefault();
        }

        public List<SqlDatabaseSchema> GetDatabases()
        {
            return GetDatabasesSchema();
        }

        public SqlDatabaseSchema GetCurrentDatabase(bool loadTables, bool loadColumns)
        {
            var database = GetDatabase(CurrentDatabase);
            if (database == null)
                return null;

            if (loadTables)
            {
                database.Tables = GetTables(loadColumns);
            }

            return database;
        }

        public SqlTableSchema GetTable(string tableName, bool loadColumns = false)
        {
            var table = GetTablesSchema(tableName).SingleOrDefault();
            if (table == null)
                return null;
            
            if (loadColumns)
                table.Columns = GetTableColumns(tableName).ToList();

            return table;
        }

        public List<SqlTableSchema> GetTables(bool loadColumns = false)
        {
            var tables = GetTablesSchema();
            
            if (loadColumns)
            {
                foreach (var table in tables)
                {
                    table.Columns = GetTableColumns(table.Name).ToList();
                }
            }
            
            return tables;
        }

        public SqlColumnSchema GetTableColumn(string columnName, string tableName)
        {
            return GetColumnsSchema(tableName, columnName).SingleOrDefault();
        }

        public List<SqlColumnSchema> GetTableColumns(string tableName)
        {
            return GetColumnsSchema(tableName);
        }

        #endregion Public

        #region Private

        private List<SqlDatabaseSchema> GetDatabasesSchema(string databaseName = null)
        {
            var restrictions = new SqlSchemaRestrictionsDatabases { Name = databaseName };

            /*
            database_name	String	Name of the database.
            */
            return GetSchemaResults(SqlClientMetaDataCollectionNames.Databases, restrictions).
                Select(row =>
                    new SqlDatabaseSchema
                    {
                        Name = Convert.ToString(row["database_name"])
                    }).ToList();
        }

        private List<SqlTableSchema> GetTablesSchema(string tableName = null)
        {
            var restrictions = new SqlSchemaRestrictionsTables { Table = tableName };

            /*
                table_catalog	String	Catalog of the table.
                table_schema	String	Schema that contains the table.
                table_name	    String	Table name.
                table_type	    String	Type of table. Can be VIEW or BASE TABLE.
             */
            return GetSchemaResults(SqlClientMetaDataCollectionNames.Tables, restrictions).
                Select(row =>
                    new SqlTableSchema
                    {
                        Name = Convert.ToString(row["table_name"]),
                        Schema = Convert.ToString(row["table_schema"])
                    }).ToList();
        }

        private List<SqlColumnSchema> GetColumnsSchema(string tableName = null, string columnName = null)
        {
            var restrictions = new SqlSchemaRestrictionsColumns { TableName = tableName, ColumnName = columnName };

            /*
            column_name	        String	Column name.
            column_default	    String	Default value of the column
            is_nullable	        String	Nullability of the column. If this column allows NULL, this column returns YES. Otherwise, No is returned.
            data_type	        String	System-supplied data type.
             */
            return GetSchemaResults(SqlClientMetaDataCollectionNames.Columns, restrictions).
                Select(row =>
                    new SqlColumnSchema
                    {
                        Name = Convert.ToString(row["column_name"]),
                        DataType = Convert.ToString(row["data_type"]),
                        DefaultValue = Convert.ToString(row["column_default"]),
                        IsNullable = Convert.ToString(row["is_nullable"]).ToLowerInvariant() == "yes"
                    }).ToList();
        }

        private List<DataRow> GetSchemaResults(string collectionName, ISqlSchemaRestrictions restrictions = null)
        {
            return GetSchema(collectionName, restrictions).Rows.Cast<DataRow>().ToList();
        }

        private DataTable GetSchema(string collectionName = null, ISqlSchemaRestrictions restrictions = null)
        {
            using (var sqlConnection = new SqlConnection(ConnectionString))
            {
                sqlConnection.Open();

                try
                {
                    if (restrictions != null)
                        return sqlConnection.GetSchema(collectionName, restrictions.Value);
                    else
                        return sqlConnection.GetSchema(collectionName);
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        #endregion Private

        #endregion Methods
    }
}
